package com.o2o.cleaning.month.platform.ebusiness_plat.brand_modular.utils

import org.apache.spark.sql.functions.lit
import org.apache.spark.sql.{DataFrame, SparkSession}

class BrandJoin {



  /**
   *
   * @param data        原始数据
   * @param spark
   * @param result_path 品牌表
   * @return
   */
  def brandResult(data: DataFrame, spark: SparkSession, result_path: DataFrame): DataFrame = {
    //    给原始数据打上品牌名称标签
    val brandName_cnJoinResult = brandName_cnJoin(data, spark, result_path)
    //    提取需要修改的品牌名称
    val errBrandResult = errBrand(brandName_cnJoinResult, spark)
    //    品牌名称修改
    val uppBrandResult = uppBrand(errBrandResult, spark)
    println("————————————————————————————————————————————————————————")
//    println(uppBrandResult.count())
//    uppBrandResult.show()
    val brandEndResult = brandEnd(brandName_cnJoinResult, uppBrandResult, spark)
    //    防晒拆分（暂时不用）
    //    val fangshai = new Fangshai
    //    val brand_fangshai = fangshai.fangshai(brandEndResult,spark)
    //    return spark.read.json(brand_fangshai.toString())
    return brandEndResult
  }

  //  给原始数据打上品牌标签
  def brandName_cnJoin(data: DataFrame, spark: SparkSession, result_path: DataFrame): DataFrame = {
    //    原始数据
    val source_data = data
      .drop("brandName_cn", "brandName_en", "brand_state", "brand_isLaoZiHao", "brand_type", "brandSourceId")
      .registerTempTable("sourcedata")

    //    品牌表
    val brand_table = result_path
      .where("brandValueId != '-1' ")
      .dropDuplicates("brandCcId")
      .registerTempTable("brandCctable")

    //    这个给原始数据打上品牌标签
    val brandjoin = spark.sqlContext.sql(
      """
        |select t1.*,
        |case when t2.brandCcId is null then upper(t1.brandName) else t2.brandName_cn end cn,
        |case when t2.brandCcId is null then upper(t1.brandName) else t2.brandName_en end en,
        |case when t2.brandCcId is null then '0' else t2.brand_state end state,
        |case when t2.brandCcId is null then '0' else t2.brand_isLaoZiHao end lzh,
        |case when t2.brandCcId is null then '-1' else t2.brand_type end type
        |
        |from
        |(select  *,concat(brandValueId,"|",firstCategoryId) brandSourceId
        |from sourcedata) t1
        |left join
        |brandCctable t2
        |on t1.brandSourceId = t2.brandCcId
      """.stripMargin)
      .drop("brandName_cn", "brandName_en", "brand_isLaoZiHao", "brand_state", "brand_type")
      .withColumnRenamed("cn", "brandName_cn")
      .withColumnRenamed("en", "brandName_en")
      .withColumnRenamed("state", "brand_state")
      .withColumnRenamed("type", "brand_type")
      .withColumnRenamed("lzh", "brand_isLaoZiHao")


    return brandjoin
  }

  def errBrand(brandName_cnJoinResult: DataFrame, spark: SparkSession): DataFrame = {
    var frame1 = spark.emptyDataFrame
    if (!brandName_cnJoinResult.columns.contains("shopId")) {
      frame1 = brandName_cnJoinResult.withColumn("shopId", lit("-1")).withColumn("shopName", lit("-1"))
    } else {
      frame1 = brandName_cnJoinResult
    }
    frame1
      .where("firstCategoryId = '10019' or firstCategoryId = '10011' or firstCategoryId = '10020' " +
        "or firstCategoryId = '10022' or firstCategoryId = '10025' or firstCategoryId = '10014' or firstCategoryId = '10017'" +
        "or firstCategoryId = '10015' or firstCategoryId = '10016' or firstCategoryId = '10021'")
      .selectExpr("upper(title) title", "upper(brandName) brandName", "upper(brandName_cn) brandName_cn","upper(split(brandName_cn,'/')[1]) en","upper(split(brandName_cn,'/')[0]) cn", "good_id", "firstCategoryId", "secondCategoryId", "thirdCategoryId", "fourthCategoryId", "shopId", "platformName", "shopName")
    .registerTempTable("brandErrorData")

    //从数据中提取需要修改的数据
    val zishengtangdata = spark.sqlContext.sql(
      """
        |select * from brandErrorData
        |where
        |--资生堂
        |title rlike '资生堂' or brandName rlike '资生堂' or brandName_cn rlike '资生堂' or title rlike 'SHISEIDO' or brandName rlike 'SHISEIDO' or brandName_cn rlike 'SHISEIDO'
        |or title rlike '安耐晒' or brandName rlike '安耐晒' or brandName_cn rlike '安耐晒' or title rlike '安热沙' or brandName rlike '安热沙' or brandName_cn rlike '安热沙'
        |or title rlike 'MOILIP' or brandName rlike 'MOILIP' or brandName_cn rlike 'MOILIP' or title rlike '卡姿兰' or brandName rlike '卡姿兰' or brandName_cn rlike '卡姿兰'
        |or (firstCategoryId = '10019' and (brandName rlike '爱敬' or brandName rlike "AGE" or brandName rlike 'KERASYS' or brandName rlike 'AEKYUNG' or brandName rlike '爱纪二十之'))
        |or brandName_cn rlike '姬芮/ZA' or brandName rlike '姬芮/ZA'
        |or brandName rlike '美赞臣' or brandName rlike 'MEAD JOHNSON' or brandName rlike 'MEADJOHNSON'
        |or brandName_cn rlike '水之密语' or brandName_cn rlike 'AQUAIR'
        |--202103
        |or cn = '伊卡璐' or en = 'HERBAL ESSENCES'
        |or brandName_cn = 'SOON PLUS' or en = 'SOON PLUS'
        |or brandName_cn = 'SUNSILK' or en = 'SUNSILK' or en = 'HAZELINE' or brandName_cn = 'HAZELINE'
        |or brandName_cn = '安宝笛/VELLMENT' or en = 'VELLMENT' or en = 'VEILMENT'
        |or brandName_cn = 'HEDONE #FASHION SNOBS' or en = 'HEDONE #FASHION SNOBS'
        |--美容美发分类拆分
        |or title rlike '水之密语' or title rlike 'AQUAIR'
        |or title rlike 'FINO' or title rlike '丝蓓绮'
        |or title rlike 'TSUBAKI' or title rlike '惠润'
        |or title rlike 'SUPER MILD' or title rlike '玛馨妮' or title rlike 'MA CHERIE'
        |--花王
        |or title rlike '花王' or brandName rlike '花王' or brandName rlike 'ALWAYS' or brandName rlike 'KAO' or title rlike 'KAO'
        |or brandName rlike '乐而雅' or title rlike '乐而雅' or title rlike '妙而舒' or brandName rlike '妙而舒' or brandName rlike 'MERRIES' or title rlike 'MERRIES'
        |or brandName_cn rlike '乐互宜'  or brandName_cn rlike '大王' or brandName_cn rlike '尤妮佳' or brandName_cn rlike '妙而舒'
        |
        |-- 根据brandName_cn对数据进行提取
        |or brandName_cn rlike '宝洁' or brandName_cn rlike '纳斯'
        |or brandName_cn rlike 'KANEBO' or brandName_cn rlike 'KRACIE' or brandName_cn rlike '肌美精'
        |or brandName_cn rlike 'M.PASSWORD' or brandName_cn rlike '棉密码'
        |or brandName_cn rlike '乔丹' or brandName_cn rlike 'JORDAN'
        |or brandName_cn rlike '百雀羚' or brandName_cn rlike 'PECHOIN'
        |or brandName_cn rlike '佳丽宝' or brandName_cn rlike '嘉娜宝'
        |or brandName_cn rlike '美士' or brandName_cn rlike '易果'
        |or (brandName_cn rlike 'CIPE' and brandName_cn rlike 'RE')
        |or (thirdCategoryId = '100190201' and brandName_cn rlike 'DHC')
        |or (thirdCategoryId = '100190201' and brandName_cn rlike 'FANCL')
        |or (thirdCategoryId = '100190102' and brandName_cn rlike 'EAUPRECIEUSE')
        |or (thirdCategoryId = '100220201' and brandName_cn = '雕')
        |or (thirdCategoryId = '100251002' and brandName_cn rlike '萱肌蜜蒸汽眼罩')
        |or (thirdCategoryId = '100251002' and brandName_cn rlike '灰姑娘')
        |
        |-- 根据brandName 对数据进行提取
        |or brandName rlike '姬芮' or brandName rlike '蒂思岚' or brandName rlike 'OPPO'
        |or brandName rlike '洁霸' or brandName rlike 'LAURIER' or brandName rlike '美舒律'
        |or brandName = 'SOLOVE' or brandName rlike '鲁茜' or  brandName = 'RÜSCH' or brandName rlike '丹尼尔惠灵顿'
        |or brandName rlike 'DANIELWELLINGTON' or  brandName rlike 'DANIEL WELLINGTON' or brandName = '三生花'
        |or brandName rlike 'SUN OZON' or brandName rlike 'YUNOS' or brandName rlike '悠娜媤'
        |or brandName rlike 'SUNOZON' or brandName rlike 'JMSOLUTION' or brandName = '毕扬'
        |or brandName rlike 'JUNOPOGA' or brandName rlike '靓采宝嘉' or brandName rlike '贝德玛'
        |or brandName rlike 'ENFAMIL' or brandName rlike '美强生'
        |
        |
        |-- 根据title对数据进行提取
        |--安肌心语
        |or title rlike 'D PROGRAM' or title rlike 'DPROGRAM'  or title rlike 'D  PROGRAM' or title rlike '安肌心语'
        |or title rlike 'D-PROGRAM' or title rlike 'D　PROGRAM'
        |
        |--珂润
        |or title rlike '珂润' or title rlike 'CUREL'
        |or title rlike '柏兰茜' or title rlike 'MILANI'
        |or (title rlike 'CAUDALIE' and title rlike '欧缇丽' and thirdCategoryId = '100190102')
        |or title rlike '多芬'
        |or title rlike '洁霸' or title rlike 'LAURIER' or title rlike '美舒律'
        |
        |--其他品牌名称
        |or brandName_cn = '-1' or brandName_cn = 'OTHER' or brandName_cn = 'OTHERS' or brandName_cn = '见描述'
        |or brandName_cn = '其他' or brandName_cn = '其它' or brandName_cn = 'OTHER'
        |
        |
        |--其他情况
        |or (title rlike '美赞臣' and  brandName rlike 'JOHNSON\\)')
        |or ((title rlike '欧莱雅'  or brandName_cn rlike '欧莱雅') and brandName = '-1' and brandName not rlike '小美盒'
        |and brandName not rlike '专业' and brandName not rlike '柏纯' and brandName not rlike '嘉瀛')
        |
        |or (brandName_cn rlike '欧莱雅' and brandName_cn not rlike '小美盒')
        |
        |-- 博朗  欧乐B
        |or (brandName_cn rlike '博朗' or brandName_cn rlike '欧乐'
        |   or brandName rlike '欧乐' or brandName rlike 'ORAL-B' or brandName_cn rlike 'ORAL-B'
        |   or brandName rlike 'BRAUN' or brandName_cn rlike 'BRAUN' or brandName rlike '博朗')
        |
        |--华为荣耀
        |or brandName_cn rlike '陈克明' or (brandName rlike '华为' or brandName rlike 'HUAWEI' or brandName_cn rlike '华为' or brandName_cn rlike 'HUAWEI'
        |or brandName_cn rlike '荣耀' or brandName_cn rlike 'HONER' or brandName rlike '荣耀' or brandName rlike 'HONER'
        |or title rlike '华为' or title rlike 'HUAWEI' or title rlike '荣耀' or title rlike 'HONER')
        |--联想
        |or (brandName rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike '联想' or brandName_cn rlike 'LENOVO'
        |or brandName rlike 'THINKPAD' or title rlike 'THINKPAD' or title rlike 'THINK PAD')
        |--梅林
        |or (fourthCategoryId = '10021020701' and brandName_cn rlike '梅林' and brandName_cn not rlike '梅林天子')
        |--金士顿
        |or (thirdCategoryId = '100150103' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        |or (thirdCategoryId = '100160703' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        |or (thirdCategoryId = '100160702' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        |
        |
      """.stripMargin)
    return zishengtangdata
  }

  def uppBrand(errBrandResult: DataFrame, spark: SparkSession): DataFrame = {

    errBrandResult.registerTempTable("uppBrand")
    val zishengtang = spark.sqlContext.sql(
      """
        |select *,
        |case
        |when brandName = 'ALWAYS' then '护舒宝'
        |when secondCategoryId = '1001904'  and brandName_cn rlike '资生堂' and upper(title) not rlike 'FINO'
        |and upper(title) not rlike 'FINO'
        |and upper(title) not rlike '丝蓓绮'
        |and upper(title) not rlike 'TSUBAKI'
        |and upper(title) not rlike '惠润'
        |and upper(title) not rlike 'SUPER MILD'
        |and upper(title) not rlike '玛馨妮'
        |and upper(title) not rlike 'MA CHERIE'
        |and upper(title) not rlike '水之密语'
        |and upper(title) not rlike 'AQUAIR'
        |then '资生堂专业美发/SHISEIDO PROFESSIONAL'
        |when secondCategoryId = '1001904'  and brandName_cn rlike '欧莱雅' and (brandName not rlike '沙龙专属' and brandName not rlike '专业美发') then "欧莱雅/L'OREAL PARIS"
        |when secondCategoryId = '1001904'  and brandName rlike '欧莱雅'  and (brandName rlike '沙龙专属' or brandName rlike '专业美发')  then "欧莱雅专业美发/L'Oreal Professional"
        |
        |when secondCategoryId = '1001904' and (upper(title) rlike '水之密语' or upper(title) rlike 'AQUAIR') then '水之密语/AQUAIR'
        |when secondCategoryId = '1001904' and upper(title) rlike 'FINO' then '芬浓/FINO'
        |when secondCategoryId = '1001904' and (upper(title) rlike '丝蓓绮' or upper(title) rlike 'TSUBAKI') then '丝蓓绮/TSUBAKI'
        |when secondCategoryId = '1001904' and (upper(title) rlike '惠润' or upper(title) rlike 'SUPER MILD') then '惠润/SUPER MILD'
        |when secondCategoryId = '1001904' and (upper(title) rlike '玛馨妮' or upper(title) rlike 'MA CHERIE') then '玛馨妮/MA CHERIE'
        |
        |when thirdCategoryId = '100190801' and brandName_cn rlike '大王' then '怡丽/ELIS'
        |when firstCategoryId = '10020'     and (brandName_cn = 'GOO．N！/大王' or brandName_cn = '大王') then '大王/GOO．N'
        |when thirdCategoryId = '100200202' and (brandName_cn rlike '尤妮佳' or brandName_cn rlike '乐互宜') then '乐互宜'
        |when thirdCategoryId = '100200201' and (brandName_cn rlike '尤妮佳' or brandName_cn rlike '乐互宜') then '尤妮佳/MOONY'
        |when thirdCategoryId = '100190801' and (brandName_cn rlike '尤妮佳' or brandName_cn rlike '乐互宜') then '苏菲/SOFY'
        |--宝洁品牌拆分
        |when brandName_cn rlike '宝洁' then (case
        |when title rlike 'BOLD' or title rlike '豪迈' or title rlike '花香子' then '豪迈/BOLD'
        |when title rlike '碧浪' or title rlike 'ARIEL' then '碧浪/ARIEL'
        |when title rlike '护舒宝' or title rlike 'ALWAYS' then '护舒宝'
        |when title rlike 'SARASA' then 'SARASA'
        |when title rlike 'COSMO'  then 'SARASA' else '豪迈/BOLD' end)
        |
        |--资生堂品牌拆分
        |
        |when cn = '伊卡璐' or en = 'HERBAL ESSENCES' then '植感哲学/HERBAL ESSENCES'
        |when brandName_cn = 'SOON PLUS' or en = 'SOON PLUS' then 'SOON+'
        |when brandName_cn = 'SUNSILK' or en = 'SUNSILK' or en = 'HAZELINE' or brandName_cn = 'HAZELINE' then '夏士莲/HAZELINE'
        |when brandName_cn = '安宝笛/VELLMENT' or title rlike '安宝笛' or en = 'VELLMENT' or cn = 'VEILMENT' then 'VEILMENT'
        |when brandName_cn = 'HEDONE #FASHION SNOBS' or en = 'HEDONE #FASHION SNOBS' then 'HEDONE'
        |when brandName_cn = 'ELASTINE ORGANIST' or en = 'ORGANIST' or brandName = 'ORGANIST' then 'ORGANIST'
        |
        |--安肌心语
        |when firstCategoryId = '10019' and (
        |     upper(title) rlike 'D PROGRAM' or upper(title) rlike "DPROGRAM"  or upper(title) rlike "D  PROGRAM"
        |     or title rlike '安肌心语' or upper(title) rlike "D-PROGRAM" or upper(title) rlike "D　PROGRAM"
        |) then "安肌心语/d program"
        |
        |when title rlike '资生堂' or brandName rlike '资生堂' or brandName_cn rlike '资生堂'
        |or brandName_cn rlike 'SHISEIDO' or brandName rlike '姬芮' or brandName_cn rlike '安热沙'
        |or brandName_cn rlike '安耐晒' or brandName_cn rlike '纳斯'
        |or brandName rlike '蒂思岚' then( case
        |when title rlike '吾诺' or title rlike 'UNO' and  (title rlike '洗颜' or title rlike '专科' or title rlike '珊珂' or title rlike 'SENKA') then '吾诺/UNO'
        |when brandName rlike '资生堂' and title rlike 'SK-II' then 'SK-II'
        |when brandName_cn rlike 'SK-II(SK-II)' then 'SK-II'
        |when brandName_cn rlike '肌肤之钥' or brandName_cn rlike 'CPB' then '肌肤之钥/CPB'
        |when brandName_cn rlike '水之密语' or brandName_cn rlike 'AQUAIR'  then '水之密语/AQUAIR'
        |when brandName_cn rlike '安耐晒' or brandName_cn rlike 'ANESSA' or brandName_cn rlike '安热沙' then '安热沙/ANESSA'
        |when brandName_cn rlike '欧珀莱' or brandName_cn rlike 'AUPRES' then '欧珀莱/AUPRES'
        |when brandName_cn rlike '蒂思岚' or title rlike '蒂思岚' or title rlike 'DICILA' then '蒂思岚/DICILA'
        |when brandName_cn rlike '怡丽丝尔'or title rlike '怡丽丝尔' or title rlike 'ELIXIR' then '怡丽丝尔/ELIXIR'
        |when brandName_cn rlike '肌水' or title rlike '肌水' or title rlike 'HADASUI' then '肌水/HADASUI'
        |when brandName_cn rlike '美润' or title rlike '美润' or (title rlike 'HAND' and title rlike 'CREAM') then '美润护手霜/HAND CREAM'
        |when brandName_cn rlike '茵芙莎'or title rlike '茵芙莎' or title rlike 'IPSA' then '茵芙莎/IPSA'
        |when brandName_cn rlike '玛馨妮'or title rlike '玛馨妮' or title rlike 'MACHERIE' or title rlike '玛宣妮' then '玛馨妮/MA CHERIE'
        |when brandName_cn rlike '心机' or title rlike '心机' or title rlike 'MAQUILLAGE' then '心机彩妆/MAQUILLAGE'
        |when brandName_cn = '娜斯' or brandName_cn = '纳斯' or brandName_cn = 'NARS' then '娜斯/NARS'
        |when brandName_cn rlike '泊美' or title rlike '泊美' or (title rlike 'PURE' and title rlike 'MILD') then '泊美/PURE&MILD'
        |when brandName_cn rlike '洗颜' or title rlike '洗颜' or title rlike '专科' or title rlike '珊珂' or title rlike 'SENKA' then '珊珂/SENKA'
        |when brandName_cn rlike '惠润' or title rlike '惠润' or (title rlike 'SUPER' and title rlike 'MILD') then '惠润/SUPER MILD'
        |when brandName_cn rlike '丝蓓' or title rlike '丝蓓' or title rlike 'TSUBAKI' then '丝蓓绮/TSUBAKI'
        |when brandName_cn rlike '吾诺' or title rlike '吾诺' or title rlike 'UNO' then '吾诺/UNO'
        |when brandName_cn rlike '悠莱' or title rlike '悠莱' or title rlike 'URARA' then '悠莱/URARA'
        |when title rlike '御银座' or title rlike 'THE GINZA'  then '御银座/THE GINZA'
        |when brandName_cn rlike '姬芮' or title rlike '姬芮' or title rlike 'ZA' then '姬芮/Za'
        |when brandName_cn rlike '嘉珂' or title rlike '嘉珂' or title rlike 'JOICO' then '嘉珂/JOICO'
        |when title rlike 'INTEGRATE' then 'INTEGRATE'
        |when brandName_cn rlike 'FINO' or title rlike 'FINO' then '芬浓/FINO'
        |when brandName_cn rlike '水之印'  or title rlike '水之印' or title rlike 'AQUALABEL' then '水之印/AQUALABEL'
        |when brandName_cn rlike '艾杜纱'  or title rlike '艾杜纱' or title rlike 'ETTUSAIS' or title rlike '艾杜紗' then '艾杜纱/ETTUSAIS'
        |when brandName_cn rlike '可悠然'  or title rlike '可悠然' or title rlike 'KUYURA' then '可悠然/KUYURA'
        |when brandName_cn rlike '臻白无瑕' or title rlike '臻白无瑕' or title rlike '臻白无暇' or title rlike 'HAKU' then '臻白无暇/HAKU'
        |when title rlike 'FRESSY' then 'FRESSY'
        |when title rlike 'FWB' then 'FWB'
        |when title rlike 'NATURGO' then 'NATURGO'
        |when title rlike 'HG' then 'HG'
        |--202103尿素和美润合并品牌
        |when title rlike '尿素' then '美润护手霜/HAND CREAM'
        |when title rlike 'SEA' and title rlike 'BREEZE' then 'SEA BREEZE'
        |when title rlike 'ADENOGEN' or title rlike '不老林'  then '不老林/ADENOGEN'
        |when title rlike 'PLAYLIST' then 'PLAYLIST'
        |when title rlike '2E' then '2E'
        |else
        |     case when secondCategoryId = '1001904' then '资生堂专业美发/SHISEIDO PROFESSIONAL' else '资生堂/Shiseido' end end)
        |--花王拆分
        |when (title rlike '花王' or brandName rlike '花王'
        |or title rlike 'KAO' or brandName rlike 'KAO'
        |or title rlike 'MERRIES' or brandName rlike 'MERRIES'
        |or title rlike '乐而雅' or brandName rlike '乐而雅'
        |or title rlike '洁霸' or brandName rlike '洁霸'
        |or title rlike 'LAURIER' or brandName rlike 'LAURIER'
        |or title rlike '美舒律' or brandName rlike '美舒律'
        |or title rlike '妙而舒' or brandName rlike '妙而舒' or brandName_cn rlike '妙而舒') then (case
        |when thirdCategoryId = '100251002' then '花王/KAO'
        |when secondCategoryId = '1002002' then '花王/MERRIES'
        |when thirdCategoryId = '100190801' then '乐而雅/LAURIER'
        |when thirdCategoryId = '100220201' then '洁霸/ATTACK' else brandName_cn end)
        |
        |--when (brandName rlike 'CIPE' and brandName rlike 'RE') then '莱斯璧/RE:CIPE'
        |when (brandName rlike 'CIPE' and brandName rlike 'RE' and brandName not rlike 'PAPA RECIPE') then '玥之秘/RE:CIPE'
        |
        |when brandName_cn = '-1' or brandName_cn = 'OTHER' or brandName_cn = 'OTHERS' or brandName_cn = '见描述'
        |or brandName_cn = '其他' or brandName_cn = '其它' or brandName_cn = 'OTHER' then '其他/OTHER'
        |
        |when brandName_cn rlike 'DHC' and thirdCategoryId = '100190201'  then "蝶翠诗/DHC"
        |when brandName_cn rlike 'FANCL' and thirdCategoryId = '100190201'  then "芳珂/FANCL"
        |when (title rlike 'CAUDALIE' or title rlike '欧缇丽') and thirdCategoryId = '100190102' then "欧缇丽/CAUDALIE"
        |when brandName_cn rlike 'EAUPRECIEUSE' and thirdCategoryId = '100190102' then "EAU PRECIEUSE"
        |when brandName_cn = '雕'  and thirdCategoryId = '100220201' then "雕牌"
        |when brandName_cn rlike '萱肌蜜蒸汽眼罩'  and thirdCategoryId = '100251002' then "萱肌蜜/CIENCIMY"
        |when brandName rlike '灰姑娘'  and thirdCategoryId = '100251002' then "灰姑娘"
        |
        |--博朗、欧乐B
        |when firstCategoryId = '10014' and thirdCategoryId = '100140408' and (brandName_cn rlike '博朗' or brandName_cn rlike '欧乐' or brandName rlike 'ORAL-B' or brandName rlike 'BRAUN') then '欧乐B/ORAL-B'
        |when firstCategoryId = '10014' and thirdCategoryId != '100140408' and (brandName rlike '博朗' or brandName rlike 'BRAUN') then '博朗/BRAUN'
        |when firstCategoryId = '10014' and thirdCategoryId != '100140408' and (brandName rlike '欧乐' or brandName rlike 'ORAL-B') then '欧乐B/ORAL-B'
        |when firstCategoryId = '10019' and thirdCategoryId = '100190702'  and (brandName_cn rlike '欧乐B' or brandName_cn = 'ORAL-B' ) then "欧乐B/ORAL-B"
        |
        |when thirdCategoryId = '100190801' and  (brandName_cn rlike 'M.PASSWORD' or brandName_cn rlike '棉密码') then '棉密码/M.PASSWORD'
        |
        |--爱敬
        |when thirdCategoryId = '100190202' and (brandName rlike '爱敬' or brandName rlike "AGE 20'S" or brandName rlike 'KERASYS' or brandName rlike 'AEKYUNG'
        |or brandName rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' ) then "爱敬/AGE 20'S"
        |when secondCategoryId = '1001904' and (brandName rlike '爱敬' or brandName rlike "AGE 20'S" or brandName rlike 'KERASYS' or brandName rlike 'AEKYUNG'
        |or brandName rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之') then '爱敬/KERASYS'
        |when thirdCategoryId != '100190202' and secondCategoryId != '1001904' and (brandName rlike '爱敬' or brandName rlike "AGE 20'S" or brandName rlike 'KERASYS' or brandName rlike 'AEKYUNG'
        |or brandName rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' ) then '爱敬/AEKYUNG'
        |--oppo
        |when thirdCategoryId = '100150101' and brandName = 'OPPO' then 'OPPO'
        |
        |--百雀羚/PECHOIN
        |when firstCategoryId = '10019' and (brandName rlike '百雀羚' or brandName = 'PECHOIN') then '百雀羚/PECHOIN'
        |when firstCategoryId = '10019' and brandName = '三生花'  then '百雀羚/PECHOIN'
        |--妮维雅
        |when firstCategoryId = '10019'     and (brandName rlike '妮维雅' or brandName = 'NIVEA') then '妮维雅/NIVEA'
        |when thirdCategoryId = '100200201' and brandName = 'SOLOVE' then 'SOLOVE'
        |when secondCategoryId = '1002003'  and (brandName rlike '鲁茜' or brandName = 'RÜSCH') then '鲁茜/RÜSCH'
        |when secondCategoryId = '1001703'  and (brandName rlike '丹尼尔惠灵顿' or brandName rlike 'DANIELWELLINGTON' or brandName rlike 'DANIEL WELLINGTON')  then "丹尼尔惠灵顿/DANIEL WELLINGTON"
        |when secondCategoryId = '1002102'  and brandName_cn rlike '陈克明'   then '陈克明/CKM'
        |
        |--title 华为荣耀
        |when firstCategoryId = '10015' and (brandName rlike '荣耀' or brandName = 'HONOR') then '荣耀/HONOR'
        |when firstCategoryId = '10015' and (brandName rlike '华为' or brandName = 'HUAWEI' or brandName_cn rlike '华为' or brandName_cn = 'HUAWEI')
        |then (case
        |when title rlike '荣耀' or title rlike 'HONOR'  then '荣耀/HONOR'
        |when firstCategoryId = '10016' and (title rlike '华为' or title rlike 'HUAWEI') and title rlike '王者荣耀' then '华为/HUAWEI'
        |when firstCategoryId = '10016' and (title rlike '荣耀' or title rlike 'HONOR')  and title not rlike '王者' then '荣耀/HONOR'
        |else brandName_cn end)
        |
        |
        |--联想、THINKPAD
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike 'LENOVO' or brandName_cn rlike '联想')
        |then (case when brandName rlike 'THINKPAD' or title rlike 'THINKPAD' or title rlike 'THINK PAD'  then 'THINKPAD' else brandName_cn end)
        |
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike 'LENOVO' or brandName_cn rlike '联想') then '联想/LENOVO'
        |
        |
        |when firstCategoryId = '10019'  and title rlike '卡姿兰' and brandName_cn not rlike '资生堂' then '卡姿兰/CARSLAN'
        |when firstCategoryId = '10019'  and brandName rlike '贝德玛' then
        |(case when title rlike '贝德玛' and brandName_cn not rlike '资生堂' then '贝德玛/BIODERMA' else brandName_cn end)
        |when firstCategoryId = '10019' and (brandName_cn rlike '资生堂' and title rlike 'MOILIP') then 'MOILIP'
        |
        |--姬芮
        |when firstCategoryId = '10019' and (brandName_cn rlike '姬芮/ZA' or brandName rlike '姬芮/ZA') then
        |(case when
        |(title rlike 'THE GINZA' or title rlike 'THE  GINZA'
        |or title rlike 'THE GINZA' or brandName rlike 'THE GINZA' or title rlike 'GINZA') then 'THE GINZA' else brandName_cn end)
        |--美赞臣
        |when firstCategoryId = '10021' and (brandName rlike '美赞臣' or brandName rlike 'MEAD JOHNSON' or brandName rlike 'MEADJOHNSON'
        | or brandName rlike 'Enfamil' or brandName rlike '美强生' or (title rlike '美赞臣' and  brandName rlike 'JOHNSON\\)')) then '美赞臣/MEAD JOHNSON'
        |--欧莱雅
        |when (title rlike '欧莱雅' or brandName_cn rlike '欧莱雅') and brandName = '-1' and brandName not rlike '小美盒'
        |and brandName not rlike '专业' and brandName not rlike '柏纯' and brandName not rlike '嘉瀛' then "欧莱雅/L'OREAL PARIS"
        |when brandName_cn rlike '欧莱雅' and brandName_cn not rlike '小美盒' then "欧莱雅/L'OREAL PARIS"
        |when firstCategoryId = '10019'  and title rlike '多芬' and brandName = '-1' then "多芬/DOVE"
        |when firstCategoryId = '10019'  and title rlike '柏兰茜' and brandName_cn not rlike '资生堂' then '柏兰茜/BOLANXI'
        |when firstCategoryId = '10019'  and title rlike 'MILANI' and brandName_cn not rlike '资生堂' then '米兰妮/MILANI'
        |
        |when thirdCategoryId = '100200402'  and (brandName rlike '哈罗闪' or brandName rlike 'SANOSAN') then '哈罗闪/SANOSAN'
        |when thirdCategoryId = '100200402'  and  brandName rlike '妈妈宝贝'  then 'MAMA&KIDS'
        |when thirdCategoryId = '100200402'  and (brandName rlike '润本' or brandName rlike 'RUNBEN') then '润本/RUNBEN'
        |
        |--
        |--佳丽宝 肌美精
        |when brandName_cn rlike '佳丽宝' or brandName_cn rlike '嘉娜宝' or brandName_cn rlike 'KANEBO' or brandName_cn rlike 'KRACIE' or brandName_cn rlike '肌美精'
        |then (case when
        |firstCategoryId = '10019' and (brandName rlike '佳丽宝' or brandName rlike 'KANEBO' or brandName rlike '嘉娜宝'
        |or brandName rlike 'SUISAI' or brandName rlike 'KANEBO SENSAI')
        |and brandName != 'KRACIE' and brandName not rlike '肌美精' then '佳丽宝/KANEBO'
        |when firstCategoryId = '10019'   and (brandName rlike '肌美精' or brandName rlike 'KRACIE')
        |and brandName != 'KANEBO' and brandName not rlike '嘉娜宝' and brandName not rlike '佳丽宝' then '肌美精/KRACIE'
        |when firstCategoryId = '10019' and  brandName rlike 'ALLIE' then '皑丽/ALLIE'
        |when firstCategoryId = '10019' and  brandName rlike '雅呵雅' then '雅呵雅丝睿/AQUASPRINA'
        |when firstCategoryId = '10019' and  brandName rlike 'AQUA SPRINA' then '雅呵雅丝睿/AQUASPRINA'
        |when firstCategoryId = '10019' and  (brandName = 'Kracie' or brandName = '肌美精' or brandName = 'Kracie 肌美精') then '肌美精/KRACIE'
        |else brandName_cn end)
        |
        |--易果
        |when firstCategoryId = '10021' and
        |brandName_cn rlike "易果" then (case
        |when title rlike "伊利" then "伊利/YILI"
        |when title rlike "蒙牛" then "蒙牛/MENGNIU"
        |when title rlike "卡士" then "卡士/CLASSY.KISS" else brandName_cn end)
        |--毕扬
        |when secondCategoryId = "1001901" and brandName = "毕扬" then "毕扬/b´Yeswo"
        |--沐浴下面
        |when thirdCategoryId = '100190601' and title rlike "LG" then (case
        |     when title rlike '欧活ORGANIST' then 'ORGANIST'
        |     when title rlike 'Wonder bath' then 'wonder bath'
        |     when title rlike '心灵小屋' then '心灵小屋'
        |     else 'VEILMENT' end
        |)
        |
        |--乔丹
        |when brandName_cn rlike '乔丹' or brandName_cn rlike 'JORDAN' then
        |(case
        |when brandName = 'AIR JORDAN' or brandName = 'ARI JORDAN' or brandName = 'JORDAN' or brandName rlike 'NIKE AIR JORDAN' or brandName rlike '美国乔丹'
        |or brandName rlike 'ALEXIS JORDAN'
        |or brandName rlike 'JORDAN（洗护）' or brandName_cn = 'JORDAN BRAND' then 'JORDAN'
        |when brandName = '乔丹' or brandName = '乔丹（QIAODAN）' or brandName = '乔丹体育' or brandName rlike '乔丹\\(MCR JOON\\)'
        |or brandName rlike '乔丹（中国）'  or brandName rlike 'QIAODAN' or brandName rlike '乔丹A361' or brandName = '—乔丹—' or brandName = '乔丹23号'
        |or brandName = '乔丹中国' or brandName = 'qiaodan' then '乔丹'
        |when brandName rlike '乔丹格兰' or brandName rlike 'JOEDON GOLAN' then '乔丹格兰/JOEDON GOLAN'
        |when brandName rlike '里奇乔丹' then '里奇乔丹/RICKY JORDAN'
        |when brandName rlike '英乔丹拉莫斯' then '英乔丹拉莫斯'
        |when brandName rlike '哈雷乔丹' then '哈雷乔丹'
        |when brandName rlike '强劲乔丹' then '强劲乔丹/QJQD'
        |when brandName rlike '麦迪乔丹' then '麦迪乔丹'
        |when brandName rlike '斯乔丹蒂' then '斯乔丹蒂'
        |when brandName rlike '绝世乔丹' then '绝世乔丹'
        |when brandName rlike '乔丹米勒' then '乔丹米勒'
        |when brandName rlike '乔丹公仔' then '乔丹公仔'
        |when brandName rlike '乔丹传人' then '乔丹传人'
        |when brandName rlike '乔丹传奇' then '乔丹传奇'
        |when brandName rlike '乔丹王国' then '乔丹王国'
        |when brandName rlike '乔丹阿玛尼' then '乔丹阿玛尼'
        |when brandName rlike '飞人乔丹' then '飞人乔丹'
        |when brandName rlike '佐敦朱迪' then '佐敦朱迪/JORDAN&JUDY'
        |when brandName rlike '乔登-诗丹' then '乔登-诗丹/JORDAN-CEADAR'
        |else brandName_cn end)
        |--梅林罐头
        |when fourthCategoryId = '10021020701' and (brandName_cn rlike '梅林' and brandName_cn not rlike  '梅林天子')
        |then (
        |     case when shopName = '梅林 京东自营旗舰店' and platformName = '京东' then '中粮梅林'
        |     when shopId = '355280013' and platformName = '天猫' then '中粮梅林'
        |     when title rlike '中粮' then '中粮梅林'
        |     when title rlike '上海' then '上海梅林'
        |     else '上海梅林' end
        |)
        |when brandName_cn = 'THE GINZA' then "The Ginza"
        |--金士顿
        |when
        | (thirdCategoryId = '100150103' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        | or (thirdCategoryId = '100160703' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        | or (thirdCategoryId = '100160702' and (brandName_cn rlike '金士顿' or brandName_cn rlike 'KINGSTON'))
        | then 'HYPERX'
        |else brandName_cn end cn_brand
        |from uppBrand
      """.stripMargin)
      .drop("brandName_cn", "aaaa", "bbbb", "cccc", "unite_brand", "brandSourceId","cn","en")
      .withColumnRenamed("cn_brand", "brandName_cn")
    zishengtang.printSchema()
    zishengtang.registerTempTable("zishengtangdatares2")
    println("=============================================")

    //    将修改完毕的brandName_cn中切出brandName_en
    val datasres = spark.sqlContext.sql(
      """
        |select *,
        |case when split(brandName_cn,"/")[1] is not null or brandName_cn rlike '/'
        |then concat(upper(split(brandName_cn,"/")[1]),"/",upper(split(brandName_cn,"/")[0]))
        |else upper(brandName_cn) end brandName_en
        |from
        |zishengtangdatares2
      """.stripMargin)

    return datasres
  }

  def brandEnd(brandName_cnJoinResult: DataFrame, uppBrandResult: DataFrame, spark: SparkSession): DataFrame = {
    brandName_cnJoinResult.dropDuplicates("good_id").registerTempTable("sourceData_cn")

    val up = spark.read.json(uppBrandResult.toJSON.rdd)
    if (up.count()==0){
      return brandName_cnJoinResult;
    }
    up
      .dropDuplicates("good_id").registerTempTable("errorDataUpp")

    val zishengtangdatares2 = spark.sql(
      """
        |select
        |t1.*,
        |case when t2.good_id is null then t1.brandName_cn else t2.brandName_cn end cn,
        |case when t2.good_id is null then t1.brandName_en else t2.brandName_en end en
        |from
        |sourceData_cn t1
        |left join
        |errorDataUpp t2
        |on t1.good_id = t2.good_id
      """.stripMargin)
      .drop("brandName_cn", "brandName_en")
      .withColumnRenamed("cn", "brandName_cn")
      .withColumnRenamed("en", "brandName_en")

    return zishengtangdatares2
  }


//  def fangshai(brandEndResult: DataFrame,spark:SparkSession) = {
//
//    val result = brandEndResult.toJSON.rdd.map(line => {
//      val jsons = JSON.parseObject(line)
//      val thirdCategoryId = jsons.getOrDefault("thirdCategoryId", "-1")
//      if (thirdCategoryId.equals("100190106")) {
//        val base_info = jsons.getOrDefault("Base_Info", "-1").toString
//        val title = jsons.getOrDefault("title", "-1").toString
//        // 有base_info的从base_info中清洗
//        if (!base_info.toString.equals("-1")) {
//          val base = jsons.getJSONObject("Base_Info")
//          val fangshai = base.getOrDefault("SPF值", "-1").toString
//          // base_info中有防晒指数的
//          val sp_fangshai = fangshai.toUpperCase()
//            .replaceAll("\\+", "")
//            .replaceAll(" ", "")
//            .replaceAll("PA", "")
//            .replaceAll("/", "")
//            .replaceAll("SPF", "")
//            .replaceAll("spf", "")
//            .replaceAll("\\(", "")
//            .replaceAll("\\（", "")
//            .replaceAll("【", "")
//            .replaceAll("\\.", "")
//            .replaceAll(":", "")
//            .replaceAll("""[\u4e00-\u9fa5 ]""", "")
//
//          if (fangshai.toUpperCase().contains("SPF") && !sp_fangshai.contains("-")) {
//            jsons.put("SPF", sp_fangshai)
//          } else {
//            title_sp(title, jsons)
//          }
//        } else {
//          title_sp(title, jsons)
//        }
//      }
//
//      jsons.toString
//    })
//
//    val result_end = spark.read.json(result)
//      .selectExpr("*", "case when SPF = null or SPF = '' or SPF rlike '其他' or SPF rlike '无' then '-1'  else SPF  end SPF_1")
//      .drop("SPF")
//      .withColumnRenamed("SPF_1", "SPF")
//
//    val brandNameJosn = result_end.toJSON.rdd.map(line => {
//      val lines = JSON.parseObject(line)
//      val SPF = lines.getOrDefault("SPF", "-1").toString
//      if (SPF.replaceAll("[0-9]+", "").length == 0) {
//        lines.put("SPF", SPF)
//      } else {
//        lines.put("SPF", "-1")
//      }
//      lines.remove("_id")
//      lines
//    })
//    brandNameJosn
//  }


}
